RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / BulkOperations / Importers /
Filename : ProductImporter.php
back
<?php

namespace App\V2\BulkOperations\Importers;

use App\Libraries\FileStorageSystem;
use App\V2\BulkOperations\ImportFactories\FieldValidatorFactory;
use App\V2\BulkOperations\ImportFields\DataImportField;
use App\V2\BulkOperations\ImportFieldTransformers\DownloadImageFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\PasswordHashFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\RoleIdFieldTransformer;
use App\V2\BulkOperations\ImportServices\ImportLoggingService;
use App\V2\BulkOperations\ImportServices\TempTableService;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class ProductImporter extends BaseImporter
{
    private $fields = [];
    private $additional_fields = [];

    public function __construct($importProfile)
    {
        parent::__construct($importProfile);
    }

    private function populateFields(){

        //product table:
        //product_type, group_id, product_category_id, product_name, description, product_sp, product_image, expires_on, is_featured
        //latitude, longitude, status

        //product_images table:
        // image_name

        //reward_settings table:
        //visible_to, visible_in_family_store, requirement_for_claim, repeate_after_days, max_claim_allowed_total, max_claim_allowed_per_user

        //reward_visibility:
        //entity_type, entity_value
        $this->fields['product_name'] = new DataImportField('product_name','product_name', 'VARCHAR(255)',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['description'] = new DataImportField('description','description', 'TEXT',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['product_category_name'] = new DataImportField('product_category_name','product_category_name', 'VARCHAR(255)',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['product_sp'] = new DataImportField('product_sp','product_sp', 'INT',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),]);
        $this->fields['product_uuid'] = new DataImportField('product_uuid','product_uuid', 'VARCHAR(255)',
            []);
        $this->fields['expires_on'] = new DataImportField('expires_on','expires_on', 'DATETIME',
            [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::FUTURE_DATE_VALIDATOR)]);
        $this->fields['is_featured'] = new DataImportField('is_featured','is_featured', 'BOOLEAN',[]);

        $this->fields['requirement_for_claim'] = new DataImportField('requirement_for_claim','requirement_for_claim', 'VARCHAR(255)',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::PRODUCT_REQUIREMENT_FOR_CLAIM_VALIDATOR)
        ]);

        $this->fields['product_image_url'] = new DataImportField('product_image_url','product_image_url', 'TEXT',
        [FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_URL),]);
        $this->fields['local_product_image_url'] = new DataImportField('local_product_image_url', 'local_product_image_url', 'TEXT', []);

        $this->fields['repeate_after_days'] = new DataImportField('repeate_after_days','repeate_after_days', 'INT',[]);
        $this->fields['visible_in_family_store'] = new DataImportField('visible_in_family_store','visible_in_family_store', 'BOOLEAN',[]);
        $this->fields['max_claim_allowed_total'] = new DataImportField('max_claim_allowed_total','max_claim_allowed_total', 'INT',[]);
        $this->fields['max_claim_allowed_per_user'] = new DataImportField('max_claim_allowed_per_user','max_claim_allowed_per_user', 'INT',[]);

        $this->fields['visible_to'] = new DataImportField('visible_to','visible_to', 'VARCHAR(100)',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::PRODUCT_VISIBLE_TO_VALIDATOR)
        ]);
        $this->fields['visible_to_country'] = new DataImportField('visible_to_country','visible_to_country', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::COUNTRY_NAME_VALIDATOR)
        ]);
        $this->fields['visible_to_zipcode'] = new DataImportField('visible_to_zipcode','visible_to_zipcode', 'TEXT',[
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::ZIPCODE_VALIDATOR)
        ]);

        $this->fields['visible_to_user_uuid'] = new DataImportField('visible_to_user_uuid','visible_to_user_uuid', 'TEXT',[]);
        $this->fields['visible_to_user_id'] = new DataImportField('visible_to_user_id','visible_to_user_id', 'TEXT',[]);

        $this->fields['latitude'] = new DataImportField('latitude','latitude', 'VARCHAR(100)',[]);
        $this->fields['longitude'] = new DataImportField('longitude','longitude', 'VARCHAR(100)',[]);

        $this->fields['created_by'] = new DataImportField('created_by','created_by', 'INT',[]);
        $this->fields['product_status'] = new DataImportField('product_status','product_status', 'VARCHAR(255)',[]);
        $this->fields['group_uuid'] = new DataImportField('group_uuid', 'group_uuid', 'VARCHAR(255)', []);

        //other fields to be populated with default value or with transformed values:
        $this->fields['product_type'] = new DataImportField('product_type', 'product_type', 'VARCHAR(255)', []);

        $this->fields['product_name']->setIsRequired(true);
        $this->fields['description']->setIsRequired(true);
        $this->fields['product_category_name']->setIsRequired(true);
        $this->fields['expires_on']->setIsRequired(true);
        $this->fields['is_featured']->setIsRequired(true);

        $this->fields['created_by']->setDefaultValue(-1);
        $this->fields['product_status']->setDefaultValue("'ACTIVE'");


        $this->fields['product_uuid']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_STR_UUID);
        $this->fields['local_product_image_url']->setTransformer(
            new DownloadImageFieldTransformer($this->fields['local_product_image_url'],
            [$this->fields['product_image_url']], 'product_images',
            'default-reward-image-color.jpg') );

    }

    private function populateProfileSpecificAdditionalFields(){
        $this->additional_fields['product_id'] = new DataImportField('product_id', 'product_id', 'INT', []);
        $this->additional_fields['reward_settings_id'] = new DataImportField('reward_settings_id', 'reward_settings_id', 'INT', []);
        $this->additional_fields['product_category_id'] = new DataImportField('product_category_id', 'product_category_id', 'INT', []);
        $this->additional_fields['group_id'] = new DataImportField('group_id', 'group_id', 'INT', []);
        $this->additional_fields['team_id'] = new DataImportField('team_id', 'team_id', 'INT', []);

    }

    protected function getFields(): array {
        if(count($this->fields) == 0)
            $this->populateFields();
        return $this->fields;
    }

    protected function getProfileSpecificAdditionalFields(): array
    {
        if(count($this->additional_fields) == 0)
            $this->populateProfileSpecificAdditionalFields();
        return $this->additional_fields;
    }

    protected function getMainTableId(){
        return 'product_id';
    }

    protected function validateProfileSpecificData()
    {

        //1, populate user id for visible to type = PARENT or selected users
        $this->populateUserIdToTempTable();

        // 3, validate product category and populate product category id
        $this->validateProductCategory();
        $this->populateProductCategoryId();

        // 4, validate group and populate group id
        $this->validateGroupIfPresent();
        $this->populateGroupId();

        // 5, populate default values for few fields
        $this->populateProductType();
    }

    public function importToMainProfileSpecificData()
    {
        //Insert into main tables:
        // 1, Insert into products table
        $this->insertIntoProductsTable();
        $this->populateProductIdToTempTable();

        // 2, insert into product_images
        $this->insertIntoProductImagesTable();

        // 3, insert into product_settings table
        $this->insertIntoRewardSettingsTable();
        $this->populateRewardSettingsIdToTempTable();

        // 4, insert into task_visibility
        $this->insertIntoRewardVisibilityTable();
    }

    private function insertIntoProductsTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `products`(uuid, product_type, group_id, product_category_id, "
            . " product_name, description, product_image, product_sp,"
            . " expires_on, latitude, longitude, created_by, status, "
            . " is_featured, created_at, import_queue_item_id) ";
        $select_sql = " SELECT product_uuid, product_type, group_id, product_category_id, "
            . " product_name, description, local_product_image_url, product_sp, "
            . " expires_on, latitude, longitude, created_by, product_status, "
            . " is_featured, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateProductIdToTempTable(){
        $field = $this->getAllFields()['product_uuid'];
        $ref_id_field = $this->getAllFields()['product_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `products` AS p  ";
        $validationQuery .= " ON p.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = p.product_id ";
        $validationQuery .= " WHERE  tmp.{$validate_is_valid_field_name} = 1 ";
        $validationQuery .= " AND p.product_id IS NOT NULL AND p.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoProductImagesTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `product_images`(product_id, image_name, created_at, import_queue_item_id) ";
        $select_sql = " SELECT product_id, local_product_image_url, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    private function insertIntoRewardSettingsTable(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `reward_settings`(reward_id, requirement_for_claim, repeate_after_days, max_claim_allowed_total, "
                        ." max_claim_allowed_per_user, created_at, import_queue_item_id, visible_to) ";
        $select_sql = " SELECT product_id, requirement_for_claim, repeate_after_days, max_claim_allowed_total, "
            ." max_claim_allowed_per_user, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id, "
            ." CASE WHEN LOCATE(',', visible_to) > 0 THEN SUBSTRING(visible_to, 1, LOCATE(',', visible_to) - 1)"
            ." ELSE visible_to END AS visible_to "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateRewardSettingsIdToTempTable(){
        $field = $this->getAllFields()['product_id'];
        $ref_id_field = $this->getAllFields()['reward_settings_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `reward_settings` AS rs  ";
        $validationQuery .= " ON rs.reward_id = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = rs.reward_settings_id ";
        $validationQuery .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";
        $validationQuery .= " AND rs.reward_settings_id IS NOT NULL AND rs.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoRewardVisibilityTable(){
        $task_visibility_type = ['ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD'
        ,'SELECTED_PARENT','COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD'];
        $visible_to_field = $this->getAllFields()['visible_to'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        foreach($task_visibility_type AS $visibility_type){
            $visible_to_type = $this->getVisibleToType($visibility_type);
            $visible_to_value_column = $this->getVisibleToValueColumn($visibility_type);

            $insert_sql = "INSERT INTO `reward_visibility`(reward_settings_id, entity_type, entity_value, created_at) ";
            $select_sql = " SELECT reward_settings_id, '$visible_to_type' AS entity_type, $visible_to_value_column AS entity_value, NOW() "
                . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
            $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
                ."AND tmp.{$visible_to_field->getColumnName()} = '$visibility_type'";

            $total_records = $this->importQueueItem->additionalDetails->total_records;
            $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );

            if(in_array($visibility_type, ['ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD'])){
                $visible_to_type='COUNTRY_SINGLE';
                $visible_to_value_column = $this->getVisibleToValueColumn($visible_to_type);

                $insert_sql = "INSERT INTO `reward_visibility`(reward_settings_id, entity_type, entity_value, created_at) ";
                $select_sql = " SELECT reward_settings_id, '$visible_to_type' AS entity_type, $visible_to_value_column AS entity_value, NOW() "
                    . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
                $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
                ."AND tmp.{$visible_to_field->getColumnName()} = '$visibility_type'";

                $total_records = $this->importQueueItem->additionalDetails->total_records;
                $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
            }
        }
    }

    private function getVisibleToType($visible_to_type){
        switch($visible_to_type){
            case 'ZIPCODE_PARENT':
            case 'ZIPCODE_PARENT_AND_CHILD':
                return 'ZIPCODE';
            case 'COUNTRY_PARENT':
            case 'COUNTRY_PARENT_AND_CHILD':
                return 'COUNTRY';
            case 'SELECTED_PARENT':
                return 'USER';
            case 'COUNTRY_SINGLE':
                return 'COUNTRY_SINGLE';
        }
        return '';
    }

    private function getVisibleToValueColumn($visible_to_type){
        switch($visible_to_type){
            case 'ZIPCODE_PARENT':
            case 'ZIPCODE_PARENT_AND_CHILD':
                return 'visible_to_zipcode';
            case 'COUNTRY_PARENT':
            case 'COUNTRY_PARENT_AND_CHILD':
                return 'visible_to_country';
            case 'SELECTED_PARENT':
                return 'visible_to_user_id';
            case 'COUNTRY_SINGLE':
                return 'visible_to_country';
        }
        return '';
    }

    protected function populateUserIdToTempTable(){
        $field = $this->getAllFields()['visible_to_user_uuid'];
        $ref_id_field = $this->getAllFields()['visible_to_user_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `users` AS u  ";
        $validationQuery .= " ON u.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = u.user_id ";
        $validationQuery .= " WHERE tmp.{$field->getColumnName()} IS NOT NULL AND u.user_id IS NOT NULL AND u.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateProductType(){
        $field = $this->getAllFields()['product_type'];
        $group_id_field = $this->getAllFields()['group_id'];
        $visible_to_field = $this->getAllFields()['visible_to'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " SET tmp.{$field->getColumnName()} = "
            ." CASE WHEN ({$visible_to_field->getColumnName()} IS NOT NULL "
            ." AND FIND_IN_SET ( {$visible_to_field->getColumnName()}, 'GLOBAL,GLOBAL_FOR_PARENT_AND_CHILD') ) "
            ." OR {$group_id_field->getColumnName()} IS NULL "
            ." THEN 'GLOBAL' ELSE 'REWARD' END ";
        $validationQuery .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

}

?>